package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.db.DBConnect;
import com.model.Rate;

public class RateControl {

	/**
	 * 插入评分
	 * @param rate
	 * @return
	 * @throws SQLException
	 */
	public boolean insertRate(Rate rate) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call insertRate("+rate.getAdmin_id()+","+rate.getDorm_id()+"," +
				"'"+rate.getRate_score()+"','"+rate.getRate_des()+"')}";
		/*String sql = "Insert into rate(admin_id, dorm_id, rate_score, rate_time, rate_des) " +
				"values("+ rate.getAdmin_id() +", "+ rate.getDorm_id() +", '"+ rate.getRate_score() +"', " +
						"getDate(), '"+ rate.getRate_des() + "')";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally {
			dbc.close();	
		}
	}
	
	/**
	 * 删除评分
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteRate(int id) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call deleteRate('"+id+"')}";
		/*
		 * String sql = "Delete from rate where rate_id = "+ id +" ";
		 */
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} finally {
			dbc.close();	
		}
	}
	
	/**
	 * 更新评分
	 * @param rate
	 * @return
	 * @throws SQLException
	 */
	public boolean updateRate(Rate rate) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call updateRate("+rate.getAdmin_id()+",'"+rate.getRate_score()+"','"+rate.getRate_des()+"',"+rate.getRate_id()+")}";
		/*String sql = "update rate set admin_id = '"+ rate.getAdmin_id() +"', " +
				"rate_score = '"+ rate.getRate_score() +"', " +
				"rate_time = getDate(), " +
				"rate_des = '"+ rate.getRate_des() +"' " +
				"where rate_id = '"+ rate.getRate_id() +"' ";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally {
			dbc.close();	
		}
	}
	
	/**
	 * 根据building_id,start_time,end_time获取评分信息
	 * @param building_id
	 * @param start_time
	 * @param end_time
	 * @return
	 */
	public ArrayList<Rate> getRateInformation(int building_id,String start_time, String end_time) {
		DBConnect dbc = new DBConnect();
		ArrayList<Rate> ratelist = new ArrayList<Rate>();
		/*
		String sql = "select * from rate,dormitory,student,building,admin where " +
				"rate.admin_id = admin.admin_id and rate.dorm_id = dormitory.dorm_id and " +
				"dormitory.dorm_building = building.building_id and dormitory.dorm_leader = student.stu_id and " +
				"rate.rate_time between  '"+ start_time +"' and '"+ end_time +"'";
		if(building_id > 0) {
			sql = sql + "and building.building_id = "+ building_id +"";
		}
		*/
		String sql = "{call getRateInformation("+ building_id +", '"+ start_time +"', '"+ end_time +"')}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Rate rate = new Rate();
				rate.setRate_id(rs.getInt("rate_id"));
				rate.setBuilding_name(rs.getString("building_name"));
				rate.setDorm_name(rs.getString("dorm_name"));
				rate.setDorm_leader(rs.getString("stu_name"));
				rate.setAdmin_name(rs.getString("admin_name"));
				rate.setRate_score(rs.getString("rate_score"));
				rate.setRate_des(rs.getString("rate_des"));
				String t = rs.getString("rate_time");
				if(t!=null)
					rate.setRate_time(t.substring(0, 16));
				ratelist.add(rate);
			}
			return ratelist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	
	/**
	 * 根据building_id,start_time,end_time获取还没有评分的寝室信息
	 * @param building_id
	 * @param start_time
	 * @param end_time
	 * @return
	 */
	public ArrayList<Rate> getUnRate(int building_id, String start_time, String end_time) {
		ArrayList<Rate> ratelist = new ArrayList<Rate>();
		String sql = "{call getUnRate(?,?,?)}";
		DBConnect dbc = new DBConnect();
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.setInt(1, building_id);
			stmt.setString(2, start_time);
			stmt.setString(3, end_time);
			ResultSet rs = stmt.executeQuery();//System.out.println("存储过程执行结束");
			while(rs.next()) {
				Rate rate = new Rate();
				rate.setDorm_id(rs.getInt("dorm_id"));
				rate.setDorm_name(rs.getString("dorm_name"));
				ratelist.add(rate);
			}
			return ratelist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	
	/**
	 * 根据学生id获取该寝室的所有评分
	 * @param stu_id
	 * @return
	 */
	public ArrayList<Rate> getRate(int stu_id) {
		DBConnect dbc = new DBConnect();
		ArrayList<Rate> ratelist = new ArrayList<Rate>();
		String sql="{call getRate("+stu_id+")}";
		/*String sql = "select * from student,rate,dormitory,admin where " +
				"rate.admin_id = admin.admin_id and rate.dorm_id = dormitory.dorm_id and " +
				"student.stu_dorm = dormitory.dorm_id and " +
				"student.stu_id = "+ stu_id +" order by rate_id desc";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Rate rate = new Rate();
				rate.setAdmin_name(rs.getString("admin_name"));
				rate.setDorm_name(rs.getString("dorm_name"));
				rate.setRate_score(rs.getString("rate_score"));
				rate.setRate_des(rs.getString("rate_des"));
				String t = rs.getString("rate_time");
				if(t!=null)
					rate.setRate_time(t.substring(0, 16));
				rate.setDorm_leader(rs.getString("dorm_leader"));
				ratelist.add(rate);
			}
			return ratelist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
}
